use crate::error::{is_unique_violation, AppErr, SqlxErr, EC_ALREADY_VOTE};
use sqlx::{Executor, Row};
use std::borrow::Cow;

use super::get_pool;

const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_vote_num (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        vote_id INTEGER NOT NULL, 
        token TEXT NOT NULL, 
        ty INTEGER NOT NULL, 
        UNIQUE (vote_id, token, ty)
    )
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

pub async fn create_with_debate(vote_id: i64, ty: u32, token: &str) -> Result<i64, AppErr> {
    let mut tx = get_pool().begin().await?;

    let ret = sqlx::query(
        "SELECT id FROM tb_vote_num WHERE vote_id = ? AND ty >= 1 AND ty <= 2 AND token = ? LIMIT 1",
    )
    .bind(vote_id)
    .bind(token)
    .fetch_optional(&mut *tx)
    .await?;

    if ret.is_some() && (ty >= 1) && (ty <= 2) {
        return Err(AppErr::Service(
            EC_ALREADY_VOTE,
            Cow::Borrowed("辩论赛只能投票一次"),
        ));
    }

    let ret = sqlx::query("INSERT INTO tb_vote_num (vote_id, ty, token) VALUES (?, ?, ?)")
        .bind(vote_id)
        .bind(ty)
        .bind(token)
        .execute(&mut *tx)
        .await.map_err(|e| {
            if is_unique_violation(&e) {
                AppErr::Service(
                    EC_ALREADY_VOTE,
                    Cow::Borrowed("已经投票过了"),
                )
            } else {
                AppErr::Sqlx(e)
            }
        })?;

    tx.commit().await?;

    Ok(ret.last_insert_rowid())
}

pub async fn count(vote_id: i64, ty: u32) -> Result<i64, AppErr> {
    let ret = sqlx::query("SELECT COUNT(*) FROM tb_vote_num WHERE vote_id = ? AND ty = ?")
        .bind(vote_id)
        .bind(ty)
        .fetch_one(get_pool())
        .await?;

    Ok(ret.get(0))
}

pub async fn vote_ids() -> Result<Vec<i64>, AppErr> {

    let rows = sqlx::query("SELECT vote_id FROM tb_vote_num GROUP BY vote_id")
        .fetch_all(get_pool())
        .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let vote_id = row.get(0);
        vec.push(vote_id);
    }
    Ok(vec)
}


